Troubleshooting group selection formulas

In some cases, no values will print when using a group selection formula, even though there are values that match the selection criteria. Typically, in these cases:

• the group selection formula references another formula
• the referenced formula is one that calculates the value of each group as a percentage of the total value of all groups (in other words a subtotal as a percentage of a grand total).

To correct a group selection formula

1. Use Xtreme.mdb to create a report that includes the following fields:

{customer.CUSTOMER NAME}
{customer.REGION}
{orders.ORDER ID}
{orders.ORDER AMOUNT}

For each order, the report shows the customer that placed the order, the region in which that customer is located, the order ID number, and the amount of the order.

2. Subtotal the {orders.ORDER AMOUNT} field using {customer.REGION} as the sort-and-group-by field.

   The program sorts the data by state and calculates a subtotal in the {orders.ORDER AMOUNT} field every time the state changes. See Subtotaling data.

3. Insert a grand total on the {orders.ORDER AMOUNT} field to see the total value of all orders placed.
4. Create a formula named Percent that calculates each subtotal as a percentage of the grand total to see the value of the orders for each region group as a percentage of all orders placed.

Sum({orders.ORDER AMOUNT}, {customer.REGION}) % Sum({orders.ORDER AMOUNT})

5. Place the formula in the Group Footer section of the report.
6. Reference the formula (@Percent) in a group selection formula that selects only those groups for which the percentage (of subtotal to grand total) is less than 5% in order to find out which regions individually contributed less than 5% of total sales:

{@Percent} < 5

When you click Save then Close, you will receive the following error message:

This formula can not be used because it must be evaluated 
later.

7. Instead of using the formula name (in this case @Percent) in the group selection formula, enter the formula itself (the formula named @Percent). Thus, instead of using the group selection formula:

{@Percent} < 5

use the group selection formula:

Sum({orders.ORDER AMOUNT}, {customer.REGION}) % Sum({orders.ORDER AMOUNT}) < 5

Now when you print, only the regions that contributed less than 5% will print.